﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PBOffice.Models;

namespace PBOffice.DAL
{
    public class FileInfoService
    {
        /// <summary>
        /// 查询出所有的文件信息
        /// </summary>
        /// <returns></returns>
        public List<FileInfo> GetAllFileInfo()
        {
            List<FileInfo> list = new List<FileInfo>();
            string sql = "select FileId,FileName,FileOwner,FilePath,Remark,IfDelete,ParentId,FileTypeId,FileTypeName,FileTypeImage,"
            + "FileTypeSuffix from FileInfo f,FileTypeInfo t where f.FileType=t.FileTypeId";
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    FileInfo file = new FileInfo();
                    FileTypeInfo type = new FileTypeInfo();
                    type.FileTypeId = (int)dr["FileTypeId"];
                    type.FileTypeName = dr["FileTypeName"].ToString();
                    type.FileTypeImage = dr["FileTypeImage"].ToString();
                    type.FileTypeSuffix = dr["FileTypeSuffix"].ToString();
                    file.FileType = type;
                    file.FileId = (int)dr["FileId"];
                    file.FileName = dr["FileName"].ToString();
                    file.FileOwner = dr["FileOwner"].ToString();
                    file.FilePath = dr["FilePath"].ToString();
                    file.Remark = dr["Remark"].ToString();
                    file.IfDelete = (int)dr["IfDelete"];
                    file.ParentId = (int)dr["ParentId"];
                    list.Add(file);
                }
            }
            return list;
        }
        /// <summary>
        /// 查询出父节点为ID的子节点
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<FileInfo> GetAllFileInfoByParentId(int id)
        {
            List<FileInfo> list = new List<FileInfo>();
            string sql = string.Format("select FileId,FileName,FileOwner,FilePath,Remark,IfDelete,ParentId,FileTypeId,FileTypeName,FileTypeImage,"
            + "FileTypeSuffix from FileInfo f,FileTypeInfo t where f.FileType=t.FileTypeId and f.ParentId={0}", id);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    FileInfo file = new FileInfo();
                    FileTypeInfo type = new FileTypeInfo();
                    type.FileTypeId = (int)dr["FileTypeId"];
                    type.FileTypeName = dr["FileTypeName"].ToString();
                    type.FileTypeImage = dr["FileTypeImage"].ToString();
                    type.FileTypeSuffix = dr["FileTypeSuffix"].ToString();
                    file.FileType = type;
                    file.FileId = (int)dr["FileId"];
                    file.FileName = dr["FileName"].ToString();
                    file.FileOwner = dr["FileOwner"].ToString();
                    file.FilePath = dr["FilePath"].ToString();
                    file.Remark = dr["Remark"].ToString();
                    file.IfDelete = (int)dr["IfDelete"];
                    file.ParentId = (int)dr["ParentId"];
                    list.Add(file);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据文件ID查一条文件信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public FileInfo GetAllFileInfoByFileId(int id)
        {
            // List<FileInfo> list = new List<FileInfo>();
            FileInfo file = new FileInfo();
            string sql = string.Format("select FileId,CreateDate,FileName,FileOwner,FilePath,Remark,IfDelete,ParentId,FileTypeId,FileTypeName,FileTypeImage,"
            + "FileTypeSuffix from FileInfo f,FileTypeInfo t where f.FileType=t.FileTypeId and f.FileId={0}", id);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {

                    FileTypeInfo type = new FileTypeInfo();
                    type.FileTypeId = (int)dr["FileTypeId"];
                    type.FileTypeName = dr["FileTypeName"].ToString();
                    type.FileTypeImage = dr["FileTypeImage"].ToString();
                    type.FileTypeSuffix = dr["FileTypeSuffix"].ToString();
                    file.FileType = type;
                    file.FileId = (int)dr["FileId"];
                    file.FileName = dr["FileName"].ToString();
                    file.CreateDate = Convert.ToDateTime(dr["CreateDate"]);
                    file.FileOwner = dr["FileOwner"].ToString();
                    file.FilePath = dr["FilePath"].ToString();
                    file.Remark = dr["Remark"].ToString();
                    file.IfDelete = (int)dr["IfDelete"];
                    file.ParentId = (int)dr["ParentId"];
                    //list.Add(file);
                }
            }
            return file;
        }
        /// <summary>
        /// 查询出所有删除的文件信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<FileInfo> GetAllFileInfoDeleted()
        {
            List<FileInfo> list = new List<FileInfo>();
            string sql = string.Format("select FileId,FileName,FileOwner,FilePath,Remark,IfDelete,ParentId,FileTypeId,FileTypeName,FileTypeImage,"
            + "FileTypeSuffix from FileInfo f,FileTypeInfo t where f.FileType=t.FileTypeId and f.IfDelete=1");
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    FileInfo file = new FileInfo();
                    FileTypeInfo type = new FileTypeInfo();
                    type.FileTypeId = (int)dr["FileTypeId"];
                    type.FileTypeName = dr["FileTypeName"].ToString();
                    type.FileTypeImage = dr["FileTypeImage"].ToString();
                    type.FileTypeSuffix = dr["FileTypeSuffix"].ToString();
                    file.FileType = type;
                    file.FileId = (int)dr["FileId"];
                    file.FileName = dr["FileName"].ToString();
                    file.FileOwner = dr["FileOwner"].ToString();
                    file.FilePath = dr["FilePath"].ToString();
                    file.Remark = dr["Remark"].ToString();
                    file.IfDelete = (int)dr["IfDelete"];
                    file.ParentId = (int)dr["ParentId"];
                    list.Add(file);
                }
            }
            return list;
        }
        public void DeleteFileInfo(int id)
        {
            string sql = string.Format("delete [FileInfo] where FileId={0}", id);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void UpdateFileInfo(FileInfo file)
        {
            string sql = "update [FileInfo] set FileName=@FileName,FileType=@FileType,Remark=@Remark, FileOwner=@FileOwner,CreateDate=@CreateDate,"
            + "ParentId=@ParentId,FilePath=@FilePath,IfDelete=@IfDelete where FileId=@FileId ";
            SqlParameter[] spa = {
                                    new SqlParameter("@FileId",file.FileId),
                                    new SqlParameter("@FileName",file.FileName),
                                    new SqlParameter("@FileType",file.FileType.FileTypeId),
                                    new SqlParameter("@Remark",file.Remark),
                                    new SqlParameter("@FileOwner",file.FileOwner),
                                    new SqlParameter("@CreateDate",file.CreateDate),
                                    new SqlParameter("@ParentId",file.ParentId),
                                    new SqlParameter("@FilePath",file.FilePath),
                                    new SqlParameter("@IfDelete",file.IfDelete)
                                };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void AddFileInfo(FileInfo file)
        {
            string sql = "insert [FileInfo] (FileName,FileType,Remark,FileOwner,CreateDate,ParentId,FilePath,IfDelete) values(@FileName,@FileType,"
            + "@Remark,@FileOwner,@CreateDate,@ParentId,@FilePath,@IfDelete)";
            SqlParameter[] spa = {
                                    new SqlParameter("@FileName",file.FileName),
                                    new SqlParameter("@FileType",file.FileType.FileTypeId),
                                    new SqlParameter("@Remark",file.Remark),
                                    new SqlParameter("@FileOwner",file.FileOwner),
                                    new SqlParameter("@CreateDate",file.CreateDate),
                                    new SqlParameter("@ParentId",file.ParentId),
                                    new SqlParameter("@FilePath",file.FilePath),
                                    new SqlParameter("@IfDelete",file.IfDelete)
                                };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
    }
}
